#region Using

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;

using Gizmox.WebGUI.Common;
using Gizmox.WebGUI.Forms;
using Gizmox.WebGUI.Common.Interfaces;

#endregion

namespace TVC.Modules.CustomControl
{
    public partial class GridFilter : UserControl
    {
        public delegate void GridFilterHandle(string strFilter, int year);
        public event GridFilterHandle GridFiterEvent;
        string sErr = "";
        string strUserID = "";
        string strTable = "";
        string strDtB = "";
        private string _strFormName;

        public GridFilter()
        {
            InitializeComponent();
        }

        private void GridFilter_Load(object sender, EventArgs e)
        {
            txtYear.Text = DateTime.Now.Year.ToString();
            //LoadData();
        }

        public void LoadData(string strUser, string strTableName, string strDB)
        {
            strUserID = strUser;
            strTable = strTableName;
            _strFormName = strTableName;
            strDtB = strDB;
            DataTable dtbShortby = new DataTable();
            BUS.zTableFieldControl ctrTableField = new BUS.zTableFieldControl();
            dtbShortby = ctrTableField.Get_zTableName(strTableName, ref sErr);

            cboField.DataSource = dtbShortby;
            cboField.DisplayMember = "LabelCaption";
            cboField.ValueMember = "FieldName";

            cboSort.DataSource = dtbShortby.Copy();
            cboSort.DisplayMember = "LabelCaption";
            cboSort.ValueMember = "FieldName";

            LoadOperator();
            loadGridFilter();
        }

        public bool LoadData(string strUser, string strFormName, String strTableName, string strDB)
        {
            strUserID = strUser;
            strTable = strTableName;
            strDtB = strDB;
            _strFormName = strFormName;
            DataTable dtbShortby = new DataTable();

            BUS.zTableFieldControl ctrTableField = new BUS.zTableFieldControl();
            dtbShortby = ctrTableField.Get_zTableName(strTableName, strFormName, ref sErr);

            cboField.DataSource = dtbShortby;
            cboField.DisplayMember = "LabelCaption";
            cboField.ValueMember = "FieldName";

            cboSort.DataSource = dtbShortby.Copy();
            cboSort.DisplayMember = "LabelCaption";
            cboSort.ValueMember = "FieldName";

            LoadOperator();

            loadGridFilter();
            return dtbShortby.Rows.Count > 0;
        }
        private void loadGridFilter()
        {
            DataTable dtbFilter = new DataTable();
            BUS.aFilterControl strTableFilter = new BUS.aFilterControl();
            dtbFilter = strTableFilter.Get_aFilterUser(strUserID, _strFormName, strDtB, ref sErr);
            dtbFilter.Columns["isNot"].DefaultValue = "Is";
            dtbFilter.Columns["isOperator"].DefaultValue = "=";
            dgvFilter.SuspendLayout();
            dgvFilter.DataSource = dtbFilter;
            dgvFilter.ResumeLayout(true);
        }

        private void LoadOperator()
        {
            DataTable dtb = new DataTable();

            dtb.Columns.Add("isOperator", typeof(string));
            dtb.Columns.Add("Name", typeof(string));
            dtb.Rows.Add("=", "Equal To");
            dtb.Rows.Add("Begin with", "Begins with");
            dtb.Rows.Add("End with", "Ends with");
            dtb.Rows.Add("Like", "Contains");
            dtb.Rows.Add("Between", "Between");
            dtb.Rows.Add("<", "Less Than");
            dtb.Rows.Add(">", "Greater Than");
            dtb.Rows.Add("<=", "Less Than or Equal");
            dtb.Rows.Add(">=", "Greater Than or Equal");
            dtb.Rows.Add("<>", "Different From");
            dtb.Rows.Add("In", "In");
            dtb.Rows.Add("EXISTS", "Exists");

            cboOperator.DataSource = dtb;
            cboOperator.DisplayMember = "Name";
            cboOperator.ValueMember = "isOperator";
        }

        private void btnClear_Click(object sender, EventArgs e)
        {
            DeleteDetail(_strFormName);
            loadGridFilter();
        }

        public DataTable GetData()
        {
            return dgvFilter.DataSource as DataTable;
        }
        public void btnOK_Click(object sender, EventArgs e)
        {
            UpdateDetail();

            string sErr = "";
            //            string strSQL = @"SELECT     aFilter.TID, aFilter.SourceContn, aFilter.Tcode, aFilter.isNot, aFilter.isOperator, aFilter.ValueFrom, aFilter.ValueTo, aFilter.isAndOr, aFilter.Field1, aFilter.Field2, aFilter.aUser, aFilter.aTable, aFilter.DB, zTableField.BoxType           
            //                FROM aFilter LEFT JOIN zTableField ON aFilter.Tcode = zTableField.FieldName AND aFilter.aTable = zTableField.zTableField
            //                WHERE ((zTableField.TableName = '{0}') 
            //                    AND (aFilter.aUser = '{1}') 
            //                    AND (aFilter.aTable = '{2}') 
            //                    AND (aFilter.DB = '{3}') 
            //                    AND ( (((aFilter.ValueFrom) Is Not Null And (aFilter.ValueFrom)<>'') ) OR (aFilter.isOperator = 'EXISTS'))) 
            //                ORDER BY aFilter.Tcode ";

            //strSQL = string.Format(strSQL, strTable, strUserID, _strFormName, strDtB);

            BUS.aFilterControl ctrFilter = new BUS.aFilterControl();
            DataTable dtb = dgvFilter.DataSource as DataTable;// ctrFilter.executeSelectQuery(strSQL, ref sErr);

            string Cond = "";
            string oField = "";

            if (dtb == null) return;
            for (int i = 0; i < dtb.Rows.Count; i++)
            {
                string str = "";
                string aField = dtb.Rows[i]["Tcode"].ToString();
                string aOperator = dtb.Rows[i]["isOperator"].ToString();
                string aValueFrom = dtb.Rows[i]["ValueFrom"].ToString();
                string aValueTo = dtb.Rows[i]["ValueTo"].ToString();
                string aType = dtb.Rows[i]["BoxType"].ToString();
                string aIsNot = dtb.Rows[i]["isNot"].ToString();

                if (aValueFrom == "" && aOperator != "EXISTS") continue;

                if (aValueFrom == "-" || aValueFrom == "*")
                {
                    switch (aValueFrom)
                    {
                        case "-":
                            //if (aOperator == "=")
                            str = "(" + aField + " is null" + ((aType == "D" || aType == "N") ? ")" : " Or " + aField + " = '')");
                            //Cond = Cond + (aField == oField ? "   Or  " : ") And (") + (aIsNot.IndexOf("Not") > 0 ? "Not " : "") + str;
                            //oField = aField;
                            break;
                        case "*":
                            //if (aOperator == "=")
                            str = "(" + aField + " is not null" + ((aType == "D" || aType == "N") ? ")" : " And " + aField + " <> '')");
                            //Cond = Cond + (aField == oField ? "   Or  " : ") And (") + (aIsNot.IndexOf("Not") > 0 ? "Not " : "") + str;
                            //oField = aField;
                            break;
                    }

                    if (str != "")
                    {
                        Cond = Cond + (Cond == "" ? "" : (aField == oField ? " Or " : " And ")) + (aIsNot.IndexOf("Not") > 0 ? "Not " : "") + str;

                        oField = aField;
                    }
                }
                else
                {
                    switch (aType)
                    {
                        case "D":    //'Date type
                            if (CheckDate(aValueFrom) == "") aValueFrom = "01/01/2100";
                            if (CheckDate(aValueFrom) == "") aValueTo = aValueFrom;
                            if (CheckDate(aValueTo) == "") aValueTo = "01/01/2100";
                            DateTime datefrom = DateTime.ParseExact(aValueFrom, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);
                            DateTime dateto = DateTime.ParseExact(aValueTo, "dd/MM/yyyy", System.Globalization.CultureInfo.InvariantCulture);

                            switch (aOperator)
                            {
                                case "Begin with"://, "End with", "Like", "In":
                                    str = "(" + aField + " = '" + datefrom.ToString("yyyy/MM/dd") + "')";
                                    break;
                                case "End with":
                                    str = "(" + aField + " = '" + datefrom.ToString("yyyy/MM/dd") + "')";
                                    break;
                                case "Like":
                                    str = "(" + aField + " = '" + datefrom.ToString("yyyy/MM/dd") + "')";
                                    break;
                                case "In":
                                    str = "(" + aField + " = '" + datefrom.ToString("yyyy/MM/dd") + "')";
                                    break;
                                case "Between":
                                    str = "(" + aField + " Between '" + datefrom + "' And '" + dateto.ToString("yyyy/MM/dd") + "')";
                                    break;
                                case "EXISTS":
                                    str = "(" + aField + " is not null" + ((aType == "D" || aType == "N") ? ")" : " And " + aField + " <> '')");
                                    break;
                                default:
                                    str = "(" + aField + " " + aOperator + " '" + datefrom.ToString("yyyy/MM/dd") + "')";
                                    break;
                            }
                            break;
                        case "N":    //'Nummeric type
                            //if (CheckNumeric(aValueFrom) == "") aValueFrom = "99999999";
                            //if (CheckNumeric(aValueTo) == "") aValueTo = aValueFrom;
                            //if (CheckNumeric(aValueTo) == "") aValueTo = "99999999";
                            switch (aOperator)
                            {
                                case "Begin with":
                                    str = "(" + aField + " = " + aValueFrom + ")";
                                    break;
                                case "End with":
                                    str = "(" + aField + " = " + aValueFrom + ")";
                                    break;
                                case "Like":
                                    str = "(" + aField + " = " + aValueFrom + ")";
                                    break;
                                case "In":
                                    string[] strin = aValueFrom.Split(',');
                                    aValueFrom = string.Join(",", strin);
                                    str = "(" + aField + " IN (" + aValueFrom + "))";
                                    break;
                                case "Between":
                                    str = "(" + aField + " Between " + aValueFrom + " And " + aValueTo + ")";
                                    break;
                                case "EXISTS":
                                    str = "(" + aField + " is not null" + ((aType == "D" || aType == "N") ? ")" : " And " + aField + " <> '')");
                                    break;
                                default:
                                    str = "(" + aField + " " + aOperator + " " + aValueFrom + ")";
                                    break;
                            }
                            break;
                        default:   //'Text type
                            switch (aOperator)
                            {
                                case "Begin with":
                                    str = "(" + aField + " Like '" + aValueFrom + "%')";
                                    break;
                                case "End with":
                                    str = "(" + aField + " Like '%" + aValueFrom + "')";
                                    break;
                                case "Between":
                                    str = "(" + aField + " Between '" + aValueFrom + "' And '" + aValueTo + "')";
                                    break;
                                case "Like":
                                    str = "(";
                                    string[] str1 = aValueFrom.Split(' ');
                                    foreach (string s in str1)
                                        if (s != "") str = str + "" + aField + " like '%" + s.Trim() + "%' And ";
                                    //Next
                                    str = str.Substring(0, str.Length - 4) + ")";
                                    break;
                                case "In":
                                    string[] strin = aValueFrom.Split(',');
                                    aValueFrom = string.Join("',N'", strin);
                                    str = "(" + aField + " IN (N'" + aValueFrom + "'))";
                                    break;
                                case "EXISTS":
                                    str = "(" + aField + " is not null" + ((aType == "D" || aType == "N") ? ")" : " And " + aField + " <> '')");
                                    break;
                                default:
                                    str = "(" + aField + " " + aOperator + " '" + aValueFrom + "')";
                                    break;
                            }
                            break;
                    }
                    if (!string.IsNullOrEmpty(oField) && aField == oField)
                    {
                        int idx = Cond.IndexOf("(" + oField);
                        int idx1 = Cond.IndexOf("((" + oField);
                        if (idx1 == -1)
                        {
                            Cond = Cond.Substring(0, idx) + "(" + Cond.Substring(idx, Cond.Length - idx);
                        }
                        if (Cond[Cond.Length - 1] == ')' && Cond[Cond.Length - 2] == ')') Cond = Cond.Substring(0, Cond.Length - 1);
                        Cond += " OR " + (aIsNot.IndexOf("Not") > 0 ? "Not " : "") + str + ")";
                    }
                    else
                    {
                        Cond += (Cond == "" ? "" : " AND ") + (aIsNot.IndexOf("Not") > 0 ? "Not " : "") + str;
                    }

                    oField = aField;
                }
            }

            if (Cond != "") Cond = "(" + Cond + ")";
            if (GridFiterEvent != null) GridFiterEvent(Cond, Convert.ToInt32((txtYear.Text == "" ? "0" : txtYear.Text)));
        }

        private string CheckDate(string strDate)
        {
            TVC.Code.cs.clsCommon checkD = new Code.cs.clsCommon();
            return checkD.CheckDate(strDate);
        }

        private string CheckNumeric(string strNumer)
        {
            try
            {
                strNumer = decimal.Parse(strNumer).ToString();
            }
            catch (Exception ex)
            {
                strNumer = "";
            }
            return strNumer;
        }

        private void dgvFilter_CellValueChanged(object sender, DataGridViewCellEventArgs e)
        {
            string strBoxType = "";

            if (dgvFilter.Columns[e.ColumnIndex].Name == "colValue1")
            {
                DataTable dtbField = (DataTable)cboField.DataSource;
                if (dtbField == null) return;
                DataRow[] dtrField = dtbField.Select(" FieldName='" + dgvFilter.Rows[e.RowIndex].Cells["cboField"].Value + "'");
                if (dtrField.Length > 0) strBoxType = dtrField[0]["BoxType"].ToString();
                if (strBoxType == "D")
                {
                    dgvFilter.Rows[e.RowIndex].Cells["colValue1"].Value = CheckDate(dgvFilter.Rows[e.RowIndex].Cells["colValue1"].Value.ToString());
                }
                else if (strBoxType == "N" && dgvFilter.Rows[e.RowIndex].Cells["cboOperator"].Value.ToString() != "In")
                {
                    dgvFilter.Rows[e.RowIndex].Cells["colValue1"].Value = CheckNumeric(dgvFilter.Rows[e.RowIndex].Cells["colValue1"].Value.ToString());
                }
            }
            else if (dgvFilter.Columns[e.ColumnIndex].Name == "colValue2")
            {
                DataTable dtbField = (DataTable)cboField.DataSource;
                if (dtbField == null) return;
                DataRow[] dtrField = dtbField.Select(" FieldName='" + dgvFilter.Rows[e.RowIndex].Cells["cboField"].Value + "'");
                if (dtrField.Length > 0) strBoxType = dtrField[0]["BoxType"].ToString();
                if (strBoxType == "D")
                {
                    dgvFilter.Rows[e.RowIndex].Cells["colValue2"].Value = CheckDate(dgvFilter.Rows[e.RowIndex].Cells["colValue2"].Value.ToString());
                }
                else if (strBoxType == "N" && dgvFilter.Rows[e.RowIndex].Cells["cboOperator"].Value.ToString() != "In")
                {
                    dgvFilter.Rows[e.RowIndex].Cells["colValue2"].Value = CheckNumeric(dgvFilter.Rows[e.RowIndex].Cells["colValue2"].Value.ToString());
                }
            }
            else if (dgvFilter.Columns[e.ColumnIndex].Name == "cboField")
            {
                DataGridViewComboBoxColumn col = dgvFilter.Columns[e.ColumnIndex] as DataGridViewComboBoxColumn;
                DataTable dtField = col.DataSource as DataTable;
                if (dtField != null)
                {
                    DataRow[] arrRow = dtField.Select(string.Format("FieldName = '{0}'", dgvFilter[e.ColumnIndex, e.RowIndex].Value));
                    if (arrRow != null && arrRow.Length > 0)
                    {
                        DataTable dt = (DataTable)dgvFilter.DataSource;
                        dt.DefaultView[e.RowIndex]["BoxType"] = arrRow[0]["BoxType"];
                    }
                }
                //cell.
            }
            else if (dgvFilter.Columns[e.ColumnIndex].Name == "cboOperator")
            {
                DataTable dtbField = (DataTable)cboField.DataSource;
                if (dtbField == null) return;
                DataRow[] dtrField = dtbField.Select(" FieldName='" + dgvFilter.Rows[e.RowIndex].Cells["cboField"].Value + "'");
                if (dtrField.Length > 0) strBoxType = dtrField[0]["BoxType"].ToString();
                if (strBoxType == "N" && dgvFilter.Rows[e.RowIndex].Cells["cboOperator"].Value.ToString() != "In")
                {
                    dgvFilter.Rows[e.RowIndex].Cells["colValue1"].Value = CheckNumeric(dgvFilter.Rows[e.RowIndex].Cells["colValue1"].Value.ToString());
                    dgvFilter.Rows[e.RowIndex].Cells["colValue2"].Value = CheckNumeric(dgvFilter.Rows[e.RowIndex].Cells["colValue2"].Value.ToString());
                }
            }
        }

        private void UpdateDetail()// Cap nhat lai bo filter
        {
            string sErr = "";
            DeleteDetail(_strFormName);
            BUS.aFilterControl ctr = new BUS.aFilterControl();
            //dgvCallSegment.TabIndex = dgvCallSegment.NewRowIndex;
            DataTable dt = dgvFilter.DataSource as DataTable;
            if (dt != null)
            {
                dt.AcceptChanges();
                //DataTable dt = dgvDetail.DataSource as DataTable;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    DTO.aFilterInfo it = new DTO.aFilterInfo(row);
                    //it.CallSeg = i + 1;
                    it.aUser = strUserID;
                    it.aTable = _strFormName;
                    it.DB = strDtB;
                    ctr.Add_aFilter(it, ref sErr);
                }
            }

        }

        private void DeleteDetail(string TableName)//
        {
            BUS.aFilterControl ctr = new BUS.aFilterControl();
            sErr = ctr.Delete_aFilterAll(strUserID, TableName, strDtB);
            if (sErr != "")
                ThrowMsg(sErr);

        }

        private void ThrowMsg(string sErr)
        {
            BaseForm x = this.Parent.Parent as BaseForm;
            if (x != null)
            {
                x.SetMsg(sErr);
            }
        }

        private void txtYear_TextChanged(object sender, EventArgs e)
        {
            txtYear.Text = CheckNumeric(txtYear.Text);
            if (txtYear.Text != "")
            {
                try
                {
                    if (int.Parse(txtYear.Text) < DateTime.MinValue.Year || int.Parse(txtYear.Text) > DateTime.MaxValue.Year)
                        txtYear.Text = "";
                }
                catch (Exception ex)
                {
                    txtYear.Text = "";
                }
            }
        }


        //private void dgvFilter_UserAddedRow(object sender, DataGridViewRowEventArgs e)
        //{
        //    if (dgvFilter.Context is IApplicationContext)
        //    {
        //        ((IApplicationContext)dgvFilter.Context).SetFocused(dgvFilter, true);
        //    }
        //}

        private void btnDirection_Click(object sender, EventArgs e)
        {
            if (btnDirection.Text == "A-Z")
            {
                btnDirection.Text = "Z-A";
            }
            else
            {
                btnDirection.Text = "A-Z";
            }
        }
        public string Direction
        {
            get
            {
                if (btnDirection.Text == "A-Z") return "ASC";
                return "DESC";
            }
        }
        public string SortField
        {
            get
            {
                return cboSort.SelectedValue + "";
            }
        }

    }
}